<?
class vehiculo{

	// Propiedades
	var $id;
	var $placa;
	var $id_marca;
	var $id_modelo;
	var $nombre_marca_modelo;
	var $id_tipo;
	var $color;
	var $anio;
	var $id_uso;
	var $carroceria;
	var $chasis;
	var $motor;
	var $historico;
	var $serial_carrozado;
	var $clase;				
	var $puestos;
	var $ejes;
	var $tara;
	var $capacidad_carga;
	var $servicio;
	var $registro;

	
	var $total;

	function get($conn, $id){
		try {
			$q = "SELECT A.*, CASE WHEN strpos(A.placa,'S/P')=1 THEN NULL ELSE A.placa END AS placa_veh, COALESCE(B.nombre,'') || ' - ' || COALESCE(C.nombre,'') AS marca_modelo FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id WHERE A.id=$id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->placa = $r->fields['placa_veh'];
				$this->id_marca = $r->fields['id_marca'];
				$this->id_modelo = $r->fields['id_modelo'];
				$this->nombre_marca_modelo = $r->fields['marca_modelo'];
				$this->id_tipo = $r->fields['id_tipo'];
				$this->color = $r->fields['id_color'];
				$this->anio = $r->fields['anio'];
				$this->id_uso = $r->fields['id_uso'];
				$this->carroceria = $r->fields['carroceria'];
				$this->chasis = $r->fields['chasis'];
				$this->motor = $r->fields['motor'];
				$this->serial_carrozado = $r->fields['serial_carrozado'];
				$this->clase = $r->fields['clase'];				
				$this->puestos = $r->fields['puestos'];
				$this->ejes = $r->fields['ejes'];
				$this->tara = $r->fields['tara'];
				$this->capacidad_carga = $r->fields['capacidad_carga'];
				$this->servicio = $r->fields['servicio'];
				$this->registro = $r->fields['registro'];

				$q="SELECT * FROM hist_placas_vehiculos WHERE id_vehiculo = $id ORDER BY fecha DESC, id DESC";
				$rH = $conn->Execute(utf8_encode($q));
				$i = 0;
				while(!$rH->EOF) {
					$HistorialAux[$i][0] = $rH->fields['fecha'];
					$HistorialAux[$i][1] = $rH->fields['placa_old'];
					$HistorialAux[$i][2] = $rH->fields['placa_new'];

					$i++;

					$rH->movenext();
				}

				$this->historico = $HistorialAux;				

			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function get_all($conn, $orden="A.id", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$emp_cod = $_SESSION['EmpresaL'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
				$q = "SELECT A.id FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
				$q = $q = "SELECT A.id FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
				$q.= "WHERE A.id::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==1){
				$q = $q = "SELECT A.id FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
				$q.= "WHERE A.placa::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
				$q = $q = "SELECT A.id FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
				$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
				$q = $q = "SELECT A.id FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
				$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text ";
				$q.= "ORDER BY $orden ";
			}

			//die($q);			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new vehiculo;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function add($conn, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $motor){
		try {
			$q = "INSERT INTO vehiculos ";
			$q.= "(placa, id_marca, id_modelo, id_tipo, id_color, anio, id_uso, carroceria, motor) ";
			$q.= "VALUES ";
			$q.= "(upper(COALESCE(NULLIF(TRIM('$placa'),''),(SELECT 'S/P' || (count(*)+1)::text FROM vehiculos WHERE placa ILIKE 'S/P%'))), NULLIF($id_marca,-1), NULLIF($id_modelo,-1), NULLIF($id_tipo,-1), NULLIF('$color',-1), NULLIF('$anio',''), NULLIF($id_uso,-1), upper(NULLIF('$carroceria','')), upper(NULLIF('$motor',''))) ";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function add2($conn, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $chasis, $motor, $serial_carrozado, $clase, $puestos, $ejes, $tara, $capacidad_carga, $servicio, $registro){
		try {
			$q = "INSERT INTO vehiculos ";
			$q.= "(placa, id_marca, id_modelo, id_tipo, id_color, anio, id_uso, carroceria, chasis, motor, serial_carrozado, clase, puestos, ejes, tara, capacidad_carga, servicio, registro) ";
			$q.= "VALUES ";
			$q.= "(upper(COALESCE(NULLIF(TRIM('$placa'),''),(SELECT 'S/P' || (count(*)+1)::text FROM vehiculos WHERE placa ILIKE 'S/P%'))), NULLIF($id_marca,-1), NULLIF($id_modelo,-1), NULLIF($id_tipo,-1), NULLIF('$color',-1), NULLIF('$anio',''), NULLIF($id_uso,-1), upper(NULLIF('$carroceria','')), upper(NULLIF('$chasis','')), upper(NULLIF('$motor','')), upper(NULLIF('$serial_carrozado','')), upper(NULLIF('$clase','')), $puestos, $ejes, upper(NULLIF('$tara','')), upper(NULLIF('$capacidad_carga','')), upper(NULLIF('$servicio','')), upper(NULLIF('$registro','')) ) ";
			//$q.=" RETURNING id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}	
	
	function add_returning($conn, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $motor){
		try {
			$q = "INSERT INTO vehiculos ";
			$q.= "(placa, id_marca, id_modelo, id_tipo, id_color, anio, id_uso, carroceria, motor) ";
			$q.= "VALUES ";
			$q.= "(upper(COALESCE(NULLIF(TRIM('$placa'),''),(SELECT 'S/P' || (count(*)+1)::text FROM vehiculos WHERE placa ILIKE 'S/P%'))), NULLIF($id_marca,-1), NULLIF($id_modelo,-1), NULLIF($id_tipo,-1), NULLIF('$color',-1), NULLIF('$anio',''), NULLIF($id_uso,-1), upper(NULLIF('$carroceria','')), upper(NULLIF('$motor',''))) ";
			//$q.=" RETURNING id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			$q2="SELECT MAX(id) AS id FROM vehiculos";
			$r2 = $conn->Execute(utf8_encode($q2));
			//return $r->fields['id'];;
			return $r2->fields['id'];;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function add_returning2($conn, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $motor, $serial_carrozado, $clase, $puestos, $ejes, $tara, $capacidad_carga, $servicio, $registro){
		try {
			$q = "INSERT INTO vehiculos ";
			$q.= "(placa, id_marca, id_modelo, id_tipo, id_color, anio, id_uso, carroceria, chasis, motor, serial_carrozado, clase, puestos, ejes, tara, capacidad_carga, servicio, registro) ";
			$q.= "VALUES ";
			$q.= "(upper(COALESCE(NULLIF(TRIM('$placa'),''),(SELECT 'S/P' || (count(*)+1)::text FROM vehiculos WHERE placa ILIKE 'S/P%'))), NULLIF($id_marca,-1), NULLIF($id_modelo,-1), NULLIF($id_tipo,-1), NULLIF('$color',-1), NULLIF('$anio',''), NULLIF($id_uso,-1), upper(NULLIF('$carroceria','')), upper(NULLIF('$chasis','')), upper(NULLIF('$motor','')), upper(NULLIF('$serial_carrozado','')), upper(NULLIF('$clase','')), $puestos, $ejes, upper(NULLIF('$tara','')), upper(NULLIF('$capacidad_carga','')), upper(NULLIF('$servicio','')), upper(NULLIF('$registro','')) ) ";
			//$q.=" RETURNING id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			$q2="SELECT MAX(id) AS id FROM vehiculos";
			$r2 = $conn->Execute(utf8_encode($q2));
			//return $r->fields['id'];;
			return $r2->fields['id'];;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}	

	function set($conn, $id, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $motor){
		try {
			$q = "UPDATE vehiculos SET placa=upper(COALESCE(NULLIF(TRIM('$placa'),''),placa)), id_marca=COALESCE(NULLIF($id_marca,-1),id_marca), id_modelo=COALESCE(NULLIF($id_modelo,-1),id_modelo), id_tipo=COALESCE(NULLIF($id_tipo,-1),id_tipo), id_color=COALESCE(NULLIF($color,-1),id_color), anio=COALESCE(NULLIF('$anio',''),anio), id_uso=COALESCE(NULLIF($id_uso,-1),id_uso), carroceria=upper(COALESCE(NULLIF(TRIM('$carroceria'),''),carroceria)), motor=upper(COALESCE(NULLIF(TRIM('$motor'),''),motor)) ";
			$q.= "WHERE id=$id";	
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_SET_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function set2($conn, $id, $placa, $id_marca, $id_modelo, $id_tipo, $color, $anio, $id_uso, $carroceria, $chasis, $motor, $serial_carrozado, $clase, $puestos, $ejes, $tara, $capacidad_carga, $servicio, $registro){
		try {
			$q = "UPDATE vehiculos SET placa=upper(COALESCE(NULLIF(TRIM('$placa'),''),placa)), id_marca=COALESCE(NULLIF($id_marca,-1),id_marca), id_modelo=COALESCE(NULLIF($id_modelo,-1),id_modelo), id_tipo=COALESCE(NULLIF($id_tipo,-1),id_tipo), id_color=COALESCE(NULLIF($color,-1),id_color), anio=COALESCE(NULLIF('$anio',''),anio), id_uso=COALESCE(NULLIF($id_uso,-1),id_uso), carroceria=upper(COALESCE(NULLIF(TRIM('$carroceria'),''),carroceria)), chasis=upper(COALESCE(NULLIF(TRIM('$chasis'),''),chasis)), motor=upper(COALESCE(NULLIF(TRIM('$motor'),''),motor)) , serial_carrozado=upper(COALESCE(NULLIF(TRIM('$serial_carrozado'),''),serial_carrozado)), clase=upper(COALESCE(NULLIF(TRIM('$clase'),''),clase)), puestos = $puestos, ejes = $ejes, tara = upper(COALESCE(NULLIF(TRIM('$tara'),''),tara)), capacidad_carga = upper(COALESCE(NULLIF(TRIM('$capacidad_carga'),''),capacidad_carga)), servicio = upper(COALESCE(NULLIF(TRIM('$servicio'),''),servicio)), registro = upper(COALESCE(NULLIF(TRIM('$registro'),''),registro)) ";
			$q.= "WHERE id=$id";	
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_SET_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function del($conn, $id){
		try {
			$q = "DELETE FROM vehiculos WHERE id=$id";
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="A.id")
	{
		$TipoE = empty($TipoE) ? 0 : $TipoE;
		if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
		}
		elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
			$q.= "WHERE A.placa::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
			$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text ";
		}
		elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM vehiculos AS A LEFT JOIN marcas AS B ON A.id_marca = B.id LEFT JOIN modelos AS C ON A.id_modelo = C.id ";
			$q.= "WHERE C.nombre::text ILIKE '%$Valor%'::text ";
		}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}
	
	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}		
	
}
?>
